﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SQLite;
using BusinessObjects;
using System.Drawing;
using System.Data;
using System.Globalization;
using Toolkit;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for trustperson
  /// </summary>
  public class TrustPersonDal : ITrustPersonLogic {

    #region - Public methods -

    /// <summary>
    /// Gets the trust persons for child.
    /// </summary>
    /// <param name="childId">The child id.</param>
    /// <returns></returns>
    public TrustPersons GetTrustPersonsForChild( int childId ) {
      TrustPersons trustPersons = new TrustPersons( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateTrustPersonsSelectCommand( connection, childId ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( ) ) {
              while ( reader.Read( ) ) {
                TrustPerson trustPerson = new TrustPerson( );
                trustPerson.Id = reader.GetInt32( reader.GetOrdinal( "TrustPersonId" ) );
                trustPerson.Name = reader.GetString( reader.GetOrdinal( "TrustPersonName" ) );
                trustPerson.FirstName = reader.GetValue( reader.GetOrdinal( "TrustPersonFirstName" ) ).ToString( );
                trustPerson.NationalNumber = reader.GetValue( reader.GetOrdinal( "TrustPersonNationalNumber" ) ).ToString( );
                trustPerson.Email = reader.GetValue( reader.GetOrdinal( "TrustPersonEmail" ) ).ToString( );
                trustPerson.Gsm = reader.GetValue( reader.GetOrdinal( "TrustPersonGsm" ) ).ToString( );
                trustPerson.Phone = reader.GetValue( reader.GetOrdinal( "TrustPersonPhone" ) ).ToString( );
                if ( reader.GetValue( reader.GetOrdinal( "TrustPersonPhoto" ) ) != DBNull.Value ) {
                  byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "TrustPersonPhoto" ) );
                  Image image = GraphicTools.Convert.ByteArrayToImage( photo );
                  if ( image != null ) {
                    trustPerson.Photo = image;
                  }
                }
                trustPerson.UseChildLink = reader.GetBoolean( reader.GetOrdinal( "TrustPersonUseChildLink" ) );
                trustPerson.Address.Street = reader.GetValue( reader.GetOrdinal( "TrustPersonStreet" ) ).ToString( );
                trustPerson.Address.Zipcode = reader.GetValue( reader.GetOrdinal( "TrustPersonZipcode" ) ).ToString( );
                trustPerson.Address.ZipcodeId = reader.GetValue( reader.GetOrdinal( "TrustPersonZipcodeId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "TrustPersonZipcodeId" ) );
                trustPerson.Address.City = reader.GetValue( reader.GetOrdinal( "TrustPersonCity" ) ).ToString( );
                trustPerson.Address.CountryName = reader.GetValue( reader.GetOrdinal( "TrustPersonCountry" ) ).ToString( );
                trustPerson.Address.CountryCode = reader.GetValue( reader.GetOrdinal( "TrustPersonCountryCode" ) ).ToString( );
                trustPerson.Address.CountryId = reader.GetValue( reader.GetOrdinal( "TrustPersonCountryId" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "TrustPersonCountryId" ) );
                trustPerson.MarkClean( );
                trustPersons.Add( trustPerson );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return trustPersons;
    }

    /// <summary>
    /// Saves the trust person.
    /// </summary>
    /// <param name="trustPerson">The trust person.</param>
    public void SaveTrustPerson( TrustPerson trustPerson ) {
      if ( trustPerson.IsNew ) {
        InsertTrustPerson( trustPerson );
      } else {
        UpdateTrustPerson( trustPerson );
      }
    }

    /// <summary>
    /// Deletes the trust person.
    /// </summary>
    /// <param name="trustPerson">The trust person.</param>
    public void DeleteTrustPerson( TrustPerson trustPerson ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateDeleteCommand( connection, trustPerson.Id ) ) {
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex );
            } finally {
              if ( connection.State != System.Data.ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
    }

    #endregion

    #region - Private methods -

    private void InsertTrustPerson( TrustPerson trustPerson ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {

          SQLiteCommand command = CreateInsertCommand( connection, trustPerson );
          command.Transaction = transaction;
          try {
            command.ExecuteNonQuery( );
            command = DataHelper.CreateSelectAfterInsertCommand( connection, "TrustPersons" );
            trustPerson.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );
            transaction.Commit( );
            trustPerson.MarkClean( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            command.Dispose( );
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }

    }

    private void UpdateTrustPerson( TrustPerson trustPerson ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateUpdateCommand( connection, trustPerson ) ) {
            command.Transaction = transaction;
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
              trustPerson.MarkClean( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex );
            } finally {
              if ( connection.State != ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }

    }

    #region - SQL statements -

    private SQLiteCommand CreateTrustPersonsSelectCommand( SQLiteConnection connection, int childId ) {

      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT TrustPersons.[ID] AS TrustPersonId,
       Children.[Id] AS TrustPersonChildId,
       TrustPersons.[Name] AS TrustPersonName,
       TrustPersons.[FirstName] AS TrustPersonFirstName,       
       TrustPersons.[NationalNumber] AS TrustPersonNationalNumber, 
       TrustPersons.[Email] AS TrustPersonEmail,       
       TrustPersons.[GSM] AS TrustPersonGsm,       
       TrustPersons.[Phone] AS TrustPersonPhone,       
       TrustPersons.[Photo] AS TrustPersonPhoto,       
       TrustPersons.[Street] AS TrustPersonStreet,
       TrustPersons.[UseChildLink] AS TrustPersonUseChildLink,
       ZipCodes.[ZipCode] AS TrustPersonZipcode,
       ZipCodes.[City] AS TrustPersonCity,
       ZipCodes.[ID] AS TrustPersonZipcodeId,
       Countries.[Country] AS TrustPersonCountry,
       Countries.[CountryCode] AS TrustPersonCountryCode,
       Countries.[Id] AS TrustPersonCountryId          

FROM TrustPersons  INNER JOIN Children ON (TrustPersons.[ChildId]=Children.Id OR TrustPersons.ChildId=Children.ChildLinkId)   
     LEFT JOIN ZipCodes ON TrustPersons.[ZipcodeId] = ZipCodes.Id
     LEFT JOIN Countries ON Countries.[ID]=ZipCodes.CountryId     
WHERE Children.[ID] = @childid";
      command.Parameters.AddWithValue( "@childid", childId );
      return command;
    }

    private SQLiteCommand CreateDeleteCommand( SQLiteConnection connection, int trustPersonId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"DELETE FROM TrustPersons WHERE TrustPersons.ID = @id";
      command.Parameters.AddWithValue( "@id", trustPersonId );
      return command;
    }

    private SQLiteCommand CreateInsertCommand( SQLiteConnection connection, TrustPerson trustPerson ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO TrustPersons (ChildId, UseChildLink, Name, FirstName, 
                              Phone, GSM, Email, NationalNumber, Photo, Street, ZipcodeId) 
                              VALUES (@childid, @usechildlink, @name, @firstname, @phone, 
                              @gsm, @email, @nationalnumber, @photo, @street, @zipcodeid)";
      command.Parameters.AddWithValue( "@childid", trustPerson.ChildId );
      command.Parameters.AddWithValue( "@usechildlink", trustPerson.UseChildLink );
      command.Parameters.AddWithValue( "@name", trustPerson.Name );
      command.Parameters.AddWithValue( "@firstname", trustPerson.FirstName );
      command.Parameters.AddWithValue( "@phone", trustPerson.Phone );
      command.Parameters.AddWithValue( "@gsm", trustPerson.Gsm );
      command.Parameters.AddWithValue( "@email", trustPerson.Email );
      command.Parameters.AddWithValue( "@nationalnumber", trustPerson.NationalNumber );
      byte[ ] photo = null;
      if ( trustPerson.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( trustPerson.Photo, trustPerson.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@street", trustPerson.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", trustPerson.Address.ZipcodeId );

      return command;
    }

    private SQLiteCommand CreateUpdateCommand( SQLiteConnection connection, TrustPerson trustPerson ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE TrustPersons SET ChildId = @childid, 
                              UseChildLink = @usechildlink, Name = @name, 
                              FirstName = @firstname, Phone = @phone, GSM = @gsm, 
                              Email = @email, NationalNumber = @nationalnumber, Photo = @photo, 
                              Street = @street, ZipcodeId = @zipcodeid
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@childid", trustPerson.ChildId );
      command.Parameters.AddWithValue( "@usechildlink", trustPerson.UseChildLink );
      command.Parameters.AddWithValue( "@name", trustPerson.Name );
      command.Parameters.AddWithValue( "@firstname", trustPerson.FirstName );
      command.Parameters.AddWithValue( "@phone", trustPerson.Phone );
      command.Parameters.AddWithValue( "@gsm", trustPerson.Gsm );
      command.Parameters.AddWithValue( "@email", trustPerson.Email );
      command.Parameters.AddWithValue( "@nationalnumber", trustPerson.NationalNumber );
      byte[ ] photo = null;
      if ( trustPerson.Photo != null ) {
        photo = GraphicTools.Convert.ImageToByteArray( trustPerson.Photo, trustPerson.Photo.RawFormat );
      }
      command.Parameters.AddWithValue( "@photo", photo );
      command.Parameters.AddWithValue( "@street", trustPerson.Address.Street );
      command.Parameters.AddWithValue( "@zipcodeid", trustPerson.Address.ZipcodeId );
      command.Parameters.AddWithValue( "@id", trustPerson.Id );

      return command;
    }

    #endregion

    #endregion

  }
}
